package personal.study.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import personal.study.form.TransactionForm;

public class DBTransaction {
	private static Log log = LogFactory.getLog(DBTransaction.class);

	public boolean insert(Connection con, TransactionForm transactionForm) throws Exception{
		boolean result = false;
		StringBuffer buffer = new StringBuffer(200);
		buffer.append("INSERT INTO Transaction(");
		buffer.append("TransCode,");
		buffer.append("ProductCode,");
		buffer.append("ProductName,");
		buffer.append("SalesCount,");
		buffer.append("CustomerName,");
		buffer.append("CustomerMobile,");
		buffer.append("PayAmount,");
		buffer.append("TransStatus,");
		buffer.append("BookDate,");
		buffer.append("FinishDate,");
		buffer.append("Flag,");
		buffer.append("Remark) ");
		
		if(log.isDebugEnabled()){
			
		}
		
		buffer.append("VALUES(?,?,?,?,?,?,?,?,?,?,?,?)");
		PreparedStatement ps = null;
		try{
			ps = con.prepareStatement(buffer.toString());
			ps.setString(1, transactionForm.getTransCode());
			ps.setString(2, transactionForm.getProductCode());
			ps.setString(3, transactionForm.getProductName());
			ps.setInt(4, transactionForm.getSalesCount());
			ps.setString(5, transactionForm.getCustomerName());
			ps.setString(6, transactionForm.getCustomerMobile());
			ps.setDouble(7, transactionForm.getPayAmount());
			ps.setInt(8, transactionForm.getTransStatus());
			ps.setDate(9, transactionForm.getBookDate());
			ps.setDate(10, transactionForm.getFinishDate());
			ps.setString(11, transactionForm.getFlag());
			ps.setString(12, transactionForm.getRemark());
			ps.executeUpdate();
		}catch(Exception e){
			log.error(e.getMessage());
		}finally{
			ps.close();
		}
		
		return result;
	}
	
	public int getCount(Connection con, String conditions) throws Exception{
		int count = 0;
		StringBuffer buffer = new StringBuffer(200);
		buffer.append("SELECT COUNT(DISTINCT TransCode) FROM Transaction WHERE ");
		buffer.append(conditions);
		
		log.debug(buffer.toString());
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			ps = con.prepareStatement(buffer.toString());
			rs = ps.executeQuery();
			rs.next();
			count = rs.getInt(1);
		}catch(Exception e){
			log.error(e.getMessage());
		}finally{
			ps.close();
			rs.close();
		}
		return count;
	}
	
	public Collection<TransactionForm> findByConditions(Connection con, String conditions) throws Exception{
		Collection<TransactionForm> col = new ArrayList<TransactionForm>();
		StringBuffer buffer = new StringBuffer(200);
		buffer.append("SELECT ");
		buffer.append("TransCode,");
		buffer.append("ProductCode,");
		buffer.append("ProductName,");
		buffer.append("SalesCount,");
		buffer.append("CustomerName,");
		buffer.append("CustomerMobile,");
		buffer.append("PayAmount,");
		buffer.append("TransStatus,");
		buffer.append("BookDate,");
		buffer.append("FinishDate,");
		buffer.append("Flag,");
		buffer.append("Remark ");
		buffer.append("FROM Transaction WHERE ");
		buffer.append(conditions);
		
		log.debug(buffer.toString());
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			ps = con.prepareStatement(buffer.toString());
			rs = ps.executeQuery();
			TransactionForm transactionForm = null;
			while(rs.next()){
				transactionForm = new TransactionForm();
				transactionForm.setTransCode(rs.getString("TransCode"));
				transactionForm.setProductCode(rs.getString("ProductCode"));
				transactionForm.setProductName(rs.getString("ProductName"));
				transactionForm.setSalesCount(rs.getInt("SalesCount"));
				transactionForm.setCustomerName(rs.getString("CustomerName"));
				transactionForm.setCustomerMobile(rs.getString("CustomerMobile"));
				transactionForm.setPayAmount(rs.getDouble("PayAmount"));
				transactionForm.setTransStatus(rs.getInt("TransStatus"));
				transactionForm.setBookDate(rs.getDate("BookDate"));
				transactionForm.setFinishDate(rs.getDate("FinishDate"));
				transactionForm.setFlag(rs.getString("Flag"));
				transactionForm.setRemark(rs.getString("Remark"));
				col.add(transactionForm);
			}
		}catch(Exception e){
			log.error(e.getMessage());
		}finally{
			ps.close();
			rs.close();
		}
		return col;
	}
	
	public Collection<TransactionForm> findByTransCode(Connection con, String conditions, int currentPage, int rowsPerPage) throws Exception{
		Collection<TransactionForm> col = new ArrayList<TransactionForm>();
		StringBuffer buffer = new StringBuffer(200);
		buffer.append("SELECT ");
		buffer.append("TransCode,");
		buffer.append("CustomerName,");
		buffer.append("CustomerMobile,");
		buffer.append("TransStatus,");
		buffer.append("BookDate,");
		buffer.append("FinishDate,");
		buffer.append("SUM(PayAmount) AS PayAmount ");
		buffer.append("FROM Transaction WHERE ");
		buffer.append(conditions);
		buffer.append(" GROUP BY TransCode,CustomerName,CustomerMobile,TransStatus,BookDate,FinishDate");
		buffer.append(" LIMIT ");
		buffer.append((currentPage - 1) * rowsPerPage);
		buffer.append(",");
		buffer.append(rowsPerPage);
		
		log.debug(buffer.toString());
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			ps = con.prepareStatement(buffer.toString());
			rs = ps.executeQuery();
			TransactionForm transactionForm = null;
			while(rs.next()){
				transactionForm = new TransactionForm();
				transactionForm.setTransCode(rs.getString("TransCode"));
				transactionForm.setCustomerName(rs.getString("CustomerName"));
				transactionForm.setCustomerMobile(rs.getString("CustomerMobile"));
				transactionForm.setTransStatus(rs.getInt("TransStatus"));
				transactionForm.setBookDate(rs.getDate("BookDate"));
				transactionForm.setFinishDate(rs.getDate("FinishDate"));
				transactionForm.setPayAmount(rs.getDouble("PayAmount"));
				col.add(transactionForm);
			}
		}catch(Exception e){
			log.error(e.getMessage());
		}finally{
			ps.close();
			rs.close();
		}
		return col;
	}
	
	public boolean update(Connection con, TransactionForm transactionForm) throws Exception{
		boolean result = false;
		StringBuffer buffer = new StringBuffer(100);
		buffer.append("UPDATE Transaction SET ");
		buffer.append("TransStatus=?,");
		buffer.append("FinishDate=? ");
		buffer.append("WHERE ");
		
		if(log.isDebugEnabled()){
			StringBuffer debugBuffer = new StringBuffer(100);
			debugBuffer.append("UPDATE Transaction SET ");
			debugBuffer.append("TransStatus=").append(transactionForm.getTransStatus()).append(",");
			debugBuffer.append("FinishDate='").append(transactionForm.getFinishDate()).append("' ");
			debugBuffer.append("WHERE TransCode='").append(transactionForm.getTransCode()).append("'");
			log.debug(debugBuffer.toString());
		}
		
		buffer.append("TransCode=?");
		
		PreparedStatement ps = null;
		try{
			ps = con.prepareStatement(buffer.toString());
			ps.setInt(1, transactionForm.getTransStatus());
			ps.setDate(2, transactionForm.getFinishDate());
			ps.setString(3, transactionForm.getTransCode());
			ps.executeUpdate();
			result = true;
		}catch(Exception e){
			log.error(e.getMessage());
		}finally{
			ps.close();
		}
		
		return result;
	}
}
